binmaster

Label:# 78 Server server-id=78# starting from which number Auto_increment_offset=1 # How many auto_increment_increment=2log-bin=mysql-binmaster-host= to increase each time 10.65.0.79master-user=rootmaster-password=123456master-port=3306log-slave-updates=1slave-skip-errors= allreplicate-do-db=repreplicate-ignore-db=mysqlmaster-connect-retry=60# 79 Server Server-id=79auto_increment_offset= 2auto_increment_increment=2log-bin=mysql-

= 800Port = 3306Socket =/tmp/mysql. sockSkip-locking######### MyISAM options #################Myisam_max_sort_file_size = 10GMyisam_max_extra_sort_file_size = 10GMyisam_sort_buffer_size = 10 MMyisam_repair_threads = 1################ Select cache options ##################Read_buffer_size = 2 MRead_rnd_buffer_size = 16 MBulk_insert_buffer_size = 5 MAuthor: Qu Baoquan, reposted to indicate the source!4/14Max_allowed_packet = 1 MTable_cache = 2048Query_cache_size = 32 MQuery_cache_limit = 2 MSort

-locking########## MyISAM Options #################Myisam_max_sort_file_size = 10GMyisam_max_extra_sort_file_size = 10GMyisam_sort_buffer_size = 10MMyisam_repair_threads = 1################ Select cache Options ##################Read_buffer_size = 2MRead_rnd_buffer_size = 16MBulk_insert_buffer_size = 5MMax_allowed_packet = 1MTable_cache = 2048Query_cache_size = 32MQuery_cache_limit = 2MSort_buffer_size = 8MJoin_buffer_size = 8MThread_concurrency = 8################ Index Cache Options ##########

-binMaster-host = 192.168.0.1Master-user = slaveMasters-password = 111111Master-port = 3306Replicate-do-db = test # Name of the database to be backed upReplicate-ignore-db = mysql # ignored DatabaseMaster-connect-retry = 60 # Time Difference (in seconds) when the master server is disconnected from the server)Log-slave-updates # This parameter must be added; otherwise, the updated records will not be added to the binary file.Slave-skip-errors # indicat

192.168.0.1-u test-p3. LOCK the master database table mysql> flush tables with read lock;4. displays the File and Position records of the master database, slave database settings use ==================================== mysql> show master status; + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_do_db | usage | + usage + ---------- + -------------- + usage + | mysql-bin.000001 | 106 | + usage + -------- + -------------- + ------------------ +5. open another

= Name of the database that does not need to be backed up. Multiple rows can be written.Slave server my. cnf# Replicate-do-db = Name of the database to be backed up by test# Replicate-ignore-db = databases ignored by mysql# Master-connect-retry = 60 if the master server is disconnected from the server, the time difference between the reconnection (seconds)You can also directly modify the my. cnf configuration file using the following settings.Log-bin = mysql-

file blocking will change.6 slave settingsLog on to the Slave database server and modify my. cnf;Server-id = 3 // 2 has been used on another server. If you need to add the Slave number and then count it in the future, it will be OK;Log-bin = mysql-binMaster-host = master database ip AddressMaster-user = slaveMasters-password = 888888Master-port = 3306Master-connect-retry = 60 // the time difference between the reconnection if the master server is dis

: mysql-bin.000151Position: 120Binlog_Do_DB: user_dbBinlog_Ignore_DB:mysql,test,information_schema,performance_schemaExecuted_Gtid_Set:1 row in set (0.00 sec)ERROR:No query specifiedmysql>4.4 configure the slave database (192.168.52.130) The Slave configuration is similar to that of the master database, as shown below: Vim /etc/my.cnf#------------------Master-Slaveconfig-----------------log-slave-updates=1replicate-same-server-id=0server-id=230 # Server IDlog-bin=/home/data/mysql/binlog/mysql-bi

'; # connection test on Slave: mysql-h 192.168.0.1-u test-p 3. LOCK the master database table mysql> flush tables with read lock; 4. displays the File and Position records of the master database, slave Database settings use ==================================== mysql> show master status; + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_do_db | usage | + usage + ---------- + -------------- + usage + | mysql-bin.000001 | 106 | + usage + -------- + -------------

heard this is very troublesome after synchronization, but I have not synchronized it;Log-bin =/var/log/mysql/updatelog // Set the generated log file name;Log-slave-updates // write the updated record to the binary file;Slave-skip-errors // skip the error and continue copying;2. create the user to use for replication;Mysql> grant replication slave on *. * to test@192.168.0.2 identified '********'3. restart mysql;/Usr/bin/mysqladmin-uroot shutdown;/Usr/bin/mysql_safe 4. back up data on the Master

MySQL does not need to talk about the unnecessary nonsense about synchronization instances. The mysql versions used in this test are 4.1.7-standard-log, the master operating system is redhat 9.0, and the slave operating system is redhat 7.3. 1. modify my. cnf on the master end and add the configuration parameters: [Mysqld] Log-bin Server-id = 1 2. modify my. cnf on the slave side and add the configuration parameters:Log-binMaster-host = mas

var3. edit/etc/my. cnfServer-id = 2Log-bin = mysql-binMaster-host = 192.168.0.1Master-user = slaveMasters-password = 111111Master-port = 3306Replicate-do-db = test # Name of the database to be backed upReplicate-ignore-db = mysql # ignored databaseMaster-connect-retry = 60 # time difference (in seconds) when the master server is disconnected from the server)Log-slave-updates # this parameter must be added; otherwise, the updated records will not be a

configuration parameters:[Mysqld]Log-binServer-id = 1 2. modify my. cnf on the slave side and add the configuration parameters:Log-binMaster-host = masterMaster-user = repMaster-password = repMaster-port = 3306Server-id = 2 The above parameters are the most basic configuration, so I will not explain them much. I will undo it to initialize the data.1. Clear all binlogs on the master. If the existing binlogs are useful, back up them first. Then, pack a

.000003# Only add, delete, and modify statements are saved.3.10: 00 data corruption, how to restore data:1) restore the data backed up/Usr/local/MySQL/bin/MySQL-Uroot-p123 test 2) restore the add, delete, and modify statements in the bin-log between and./Usr/local/MySQL/bin/mysqlbinlog -- no-defaults -- stop-position = 367 mysql-bin.000003 |/usr/local/MySQL/bin/MySQL-uroot-p123 Test -- Stop-position: the last log of the logbin to be restored. You can query the location of the log file, and -- st

but th Is does to always make sense; Please check the manual before using it)The description side has the same server-id from the MY.CNF in the server.Solution:Modify the Server-id in the MY.CNF and restart the database service. MY.CNF file Default has server-id=1Other NotesPrimary server My.cnf#binlog-do-db= The name of the database you want to back up, write multiple lines#binlog-ignore-db= database names that do not need to be backed up, write multiple linesMY.CNF from server# Replicate-do-d

user;Mysql>grant replication Slave on * * to [e-mail protected] identified by ' ******** '3, restart MySQL;/usr/bin/mysqladmin-uroot shutdown;/usr/bin/mysql_safe 4. Now back up the data on master;After locking I directly tar.gz data this library file;Mysql>flush TABLES with READ LOCK;Cd/var/lib/mysqlTar data.tar.gz dataThen the remote SCP was executed directly;SCP./data.tar.gz [Email Protected]:/var/lib/mysql5, login Slave database server, modify the MY.CNF;Server-id = 3//2 has been used on ano

// skip the error and continue copying;2. Create the user to use for replication;Mysql> grant replication slave on *. * to test@192.168.0.2 identified '********'3. Restart mysql;/Usr/bin/mysqladmin-uroot shutdown;/Usr/bin/mysql_safe 4. Back up data on the Master node;After the locks, I directly lock the tar.gz data library file;Mysql> flush tables with read lock;Cd/var/lib/mysqlTar data.tar.gz dataThen the remote scp is directly executed;Scp./data.tar.gz root@192.168.0.2:/var/lib/mysql5. log on

################# Master ###################### ###Server-id = 1Log-bin =/usr/local/mysql/binlog/master-binBinlog_format = mixedRelay-log =/usr/local/mysql/binlog/mysqld-relay-bin##################### Slave##########################Relay-log =/usr/local/mysql/binlog/slave-relay-binMaster-host = 192.168.10.198Master-user = qiangaoMasters-password = 123456Master-connect-retry = 10############# Log ##################Log-error =/var/log/mysql. log########

-medium.cnf exists between 32m--64m and is used with other services, such as webMy-large.cnf memory has m mainly to provide database servicesMy-huge.cnf memory has 1g to 2G, mainly provide database services4. Modify the my. CNF file on 192.168.1.56. Here we name it Server 1.# Vi/etc/My. CNFAdd the following content in [mysqld:Log-bin = mysql-binMaster-host = 192.168.157Master-user = tongbuMasters-Password = 123456Master-Port = 3306BINLOG-do-DB = Bak B

######### MyISAM options #################Myisam_max_sort_file_size = 10GMyisam_max_extra_sort_file_size = 10GMyisam_sort_buffer_size = 10 MMyisam_repair_threads = 1################ Select cache options ##################Read_buffer_size = 2 MRead_rnd_buffer_size = 16 MBulk_insert_buffer_size = 5 M4Max_allowed_packet = 1 MTable_cache = 2048Query_cache_size = 32 MQuery_cache_limit = 2 MSort_buffer_size = 8 MJoin_buffer_size = 8 MThread_concurrency = 8############### Index cache options ##########